﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;

namespace KTSonline.administrator
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            KTSonlineDataContext db = new KTSonlineDataContext();
            db.ExecuteCommand(CKEditor1.Text);
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            KTSonlineDataContext db = new KTSonlineDataContext();
            db.MAY_ANHs.DeleteAllOnSubmit(db.MAY_ANHs);
            db.MAY_QUAY_PHIMs.DeleteAllOnSubmit(db.MAY_QUAY_PHIMs);
            db.MAY_NGHE_NHACs.DeleteAllOnSubmit(db.MAY_NGHE_NHACs);
            foreach (IDENTITY_CONTROL ic in db.IDENTITY_CONTROLs)
                ic.sothutu = 0;
            db.SubmitChanges();
        }

        protected void Button3_Click(object sender, EventArgs e)
        {
            //String filename = Server.MapPath(@"~/temp.xls");
            //string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 8.0;";
            string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=temp.xls;Extended Properties=Excel 8.0;";

            String[] lstsheet = new String[] { "Máy ảnh", "Máy quay phim", "Máy nghe nhạc", "Loa", "Tai nghe", "Máy tính bảng", "Ổ cứng" };
            String[] lsttable = new String[] { "MAY_ANH", "MAY_QUAY_PHIM", "MAY_NGHE_NHAC", "LOA", "TAI_NGHE", "MAY_TINH_BANG", "O_CUNG" };

            String select = "SELECT id, [Tên máy] as tenmay, [Giá bán] as giaban, Thumbnail, nsx FROM [";
            String range = "$B1:T100] WHERE id <> ''";
            OleDbDataAdapter oAdapter;
            DataSet ds = new DataSet();
            for (int i = 0; i < lstsheet.Count(); i++)
            {
                oAdapter = new OleDbDataAdapter(select + lstsheet[i] + range, strConn);
                oAdapter.Fill(ds, lsttable[i]);
            }

            KTSonlineDataContext db = new KTSonlineDataContext();
            foreach (DataTable tb in ds.Tables)
            {
                foreach (DataRow dr in tb.Rows)
                {
                    String sql = String.Format("INSERT INTO {0}(id, tenmay, giaban, thumbnail, nhasanxuat) VALUES ('{1}','{2}',{3},'{4}', {5})", tb.TableName, dr["id"], dr["tenmay"], dr["giaban"], dr["Thumbnail"], dr["nsx"]);
                    db.ExecuteCommand(sql);
                    db.IDENTITY_CONTROLs.Where(c => c.tenbang == tb.TableName).First().sothutu++;
                    db.SubmitChanges();
                }
            }
            
        }
            
    }
}
